var express = require('express');
var router = express.Router();
var datasetdao = require('../dao/datasetdao');
var filedao = require('../dao/filedao');
var fileresourcedao = require('../dao/fileresourcedao');
var utils = require('../common/utils');
var excel = require('../common/excel');

var services = require('../services/fileimport');

router.get('/manage', function (req, res, next) {
    res.render('dataset/list');
});

router.get('/dsbind', function (req, res, next) {
    res.render('dataset/dsbind');
});
router.get('/export', function (req, res, next) {
    res.render('dataset/export');
});
router.get('/fsbind', function (req, res, next) {
    res.render('dataset/fsbind');
});
router.get('/add', function (req, res, next) {
    res.render('dataset/add');
});
router.get('/resourcelist', function (req, res, next) {
    res.render('dataset/resourcelist');
});

router.get('/folder', function (req, res, next) {
    res.render('dataset/folder');
});
router.get('/condition', function (req, res, next) {
    res.render('dataset/condition');
});
router.get('/folderlist', function (req, res, next) {
    filedao.getdirectory().then(function (data) {
        res.json({
            "code": 0,
            "msg": "",
            data: data
        });
    }, function (error) {
        res.json({
            "code": 1,
            "msg": error.message
        });
    })
});

router.get('/list', function (req, res, next) {
    var page = req.query.page || 1; //页号
    var limit = req.query.limit || 10; //页大小
    var key = req.query.key || ''; //查询条件
    var condition = {
        page: parseInt(page),
        limit: parseInt(limit),
        key: key,
        userid: req.session.user.userid
    };
    datasetdao.getlist(condition).then(function (data) {
        res.json({
            "code": 0,
            "msg": "",
            count: data[1][0].total,
            data: data[0]
        });
    }, function (error) {
        res.json({
            "code": 1,
            "msg": error.message
        });
    })
});


router.post('/add', function (req, res, next) {
    var dsname = req.body.dsname || '';
    var description = req.body.description || '';
    var dsid = utils.generalShortUrl(utils.generalUUID()).toLowerCase();
    var dstags = req.body.dstags || '';
    var userid = req.session.user.userid;

    var params = {
        dsname: dsname,
        description: description,
        dsid: dsid,
        dstags: dstags,
        dstype: -1, //未绑定数据源
        status: 0, //任务未开始
        userid: userid, //数据集创建者
        createtype: 1, //手动创建
        dstable: 'dstable_' + dsid

    };

    datasetdao.add(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

/* 
 *数据集绑定
 */
router.post('/binddataset', function (req, res, next) {
    var dsid = req.body.dsid;
    var ids = req.body['ids[]'];
    var userid = req.session.user.userid;
    var dstype = req.body.dstype;

    if (typeof (ids) == 'string') {
        ids = new Array(ids);
    }
    var uniontables = [];
    ids.forEach(element => {
        uniontables.push('dstable_' + element);
    });
    var params = {
        dsid: dsid,
        ids: ids,
        userid: userid,
        dstype: dstype,
        uniontables: uniontables
    };

    fileresourcedao.binddataset(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        if (err.code == 'ER_DUP_ENTRY') {
            return res.json({
                code: -1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

/* 
 *数据集导出
 */
router.get('/binddataset_export', function (req, res, next) {
    var dsid = req.query.dsid;
    var ids = req.query['ids'];
    var userid = req.session.user.userid;
    var dstype = req.query.dstype;

    if (typeof (ids) == 'string') {
        ids = ids.split(',');
    }
    var uniontables = [];
    ids.forEach(element => {
        uniontables.push('dstable_' + element);
    });
    var params = {
        dsid: dsid,
        ids: ids,
        userid: userid,
        dstype: dstype,
        uniontables: uniontables
    };

    fileresourcedao.binddataset_export(params).then(function (data) {
        console.log(data);
        var result = excel.export_excel(data[0][0]);
        res.setHeader('Content-Type', 'application/vnd.openxmlformats;charset=utf-8');
        res.setHeader("Content-Disposition", "attachment; filename=dataset.xlsx");
        res.end(result, 'binary');
        /* if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        } */
    }, function (err) {
        if (err.code == 'ER_DUP_ENTRY') {
            return res.json({
                code: -1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

/* 
 *数据集预检索
 */
router.post('/binddataset_search', function (req, res, next) {
    var dsid = req.body.dsid;
    var ids = req.body.ids;
    var userid = req.session.user.userid;
    var dstype = req.body.dstype;

    ids = JSON.parse(ids);
    var inputdsids = [];
    //union tables
    var union_tables = [];
    ids.forEach(e => {
        inputdsids.push(e.dsid);
        var andsql = [],
            orsql = [];
        for (let i = 0; i < e.data.length; i++) {
            const data = e.data[i];

            const where = {};
            for (var p in data) {
                var name = p; //属性名称 
                var value = data[p]; //属性对应的值 
                where[name] = data[p];
            }

            if (where.condition == 'and') {
                andsql.push(' JSON_SEARCH(label->\'$.outputs[*].name\',\'one\',\'' + where.tag + '\') is not null ');
            } else if (where.condition == 'or') {
                orsql.push('  JSON_SEARCH(label->\'$.outputs[*].name\',\'one\',\'' + where.tag + '\') is not null ');
            }
        }
        union_tables.push({
            'table': 'dstable_' + e.dsid,
            where: andsql.join(' and ') + ((andsql.length > 0 && orsql.length > 0) ? ' or ' : '') + orsql.join(' or ')
        });
    });
    var params = {
        dsid: dsid,
        userid: userid,
        dstype: dstype,
        ids: ids,
        union_tables: union_tables,
        inputdsids: inputdsids
    };
    fileresourcedao.binddataset_search(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        if (err.code == 'ER_DUP_ENTRY') {
            return res.json({
                code: -1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

/* 
 *数据集高级检索导出
 */
router.get('/binddataset_search_export', function (req, res, next) {
    var dsid = req.query.dsid;
    var ids = req.query.ids;
    var userid = req.session.user.userid;
    var dstype = req.query.dstype;

    ids = JSON.parse(ids);
    var inputdsids = [];
    //union tables
    var union_tables = [];
    ids.forEach(e => {
        inputdsids.push(e.dsid);
        var andsql = [],
            orsql = [];
        for (let i = 0; i < e.data.length; i++) {
            const data = e.data[i];

            const where = {};
            for (var p in data) {
                var name = p; //属性名称 
                var value = data[p]; //属性对应的值 
                where[name] = data[p];
            }

            if (where.condition == 'and') {
                andsql.push(' JSON_SEARCH(label->\'$.outputs[*].name\',\'one\',\'' + where.tag + '\') is not null ');
            } else if (where.condition == 'or') {
                orsql.push('  JSON_SEARCH(label->\'$.outputs[*].name\',\'one\',\'' + where.tag + '\') is not null ');
            }
        }
        union_tables.push({
            'table': 'dstable_' + e.dsid,
            where: andsql.join(' and ') + ((andsql.length > 0 && orsql.length > 0) ? ' or ' : '') + orsql.join(' or ')
        });
    });
    var params = {
        dsid: dsid,
        userid: userid,
        dstype: dstype,
        ids: ids,
        union_tables: union_tables,
        inputdsids: inputdsids
    };
    fileresourcedao.binddataset_search_export(params).then(function (data) {
        console.log(data);

        var result = excel.export_excel(data[0][0][0]);
        res.setHeader('Content-Type', 'application/vnd.openxmlformats;charset=utf-8');
        res.setHeader("Content-Disposition", "attachment; filename=dataset.xlsx");
        res.end(result, 'binary');

    }, function (err) {
        if (err.code == 'ER_DUP_ENTRY') {
            return res.json({
                code: -1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

/* 
 *数据集高级检索预显示
 */
router.post('/binddataset_showdetail', function (req, res, next) {
    var dsid = req.body.dsid;
    var ids = req.body.ids;
    var userid = req.session.user.userid;
    var dstype = req.body.dstype;

    ids = JSON.parse(ids);
    var inputdsids = [];
    //union tables
    var union_tables = [];
    ids.forEach(e => {
        inputdsids.push(e.dsid);
        var andsql = [],
            orsql = [];
        for (let i = 0; i < e.data.length; i++) {
            const data = e.data[i];

            const where = {};
            for (var p in data) {
                var name = p; //属性名称 
                var value = data[p]; //属性对应的值 
                where[name] = data[p];
            }

            if (where.condition == 'and') {
                andsql.push(' JSON_SEARCH(label->\'$.outputs[*].name\',\'one\',\'' + where.tag + '\') is not null ');
            } else if (where.condition == 'or') {
                orsql.push('  JSON_SEARCH(label->\'$.outputs[*].name\',\'one\',\'' + where.tag + '\') is not null ');
            }
        }
        union_tables.push({
            'table': 'dstable_' + e.dsid,
            where: andsql.join(' and ') + ((andsql.length > 0 && orsql.length > 0) ? ' or ' : '') + orsql.join(' or ')
        });
    });
    var params = {
        dsid: dsid,
        userid: userid,
        dstype: dstype,
        ids: ids,
        union_tables: union_tables,
        inputdsids: inputdsids
    };
    fileresourcedao.binddataset_showdetail(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1,
                totalcount: data[0][0][0].totalcount
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        if (err.code == 'ER_DUP_ENTRY') {
            return res.json({
                code: -1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

//folder部分

router.get('/fslist', function (req, res, next) {
    var page = req.query.page || 1; //页号
    var limit = req.query.limit || 10; //页大小
    var key = req.query.key || ''; //查询条件
    var resourceid = req.query.resourceid || '-1';
    var showfile = req.query.showfile == 'false' ? 0 : 1;
    var condition = {
        page: parseInt(page),
        limit: parseInt(limit),
        key: key,
        resourceid: resourceid,
        showfile: showfile
    };
    fileresourcedao.getlist(condition).then(function (data) {
        res.json({
            "code": 0,
            "msg": "",
            count: data[1][0].total,
            data: data[0]
        });
    }, function (error) {
        res.json({
            "code": 1,
            "msg": error.message
        });
    })
});

router.post('/bindfolders', function (req, res, next) {
    var dsid = req.body.dsid;
    var folderids = req.body['ids[]'];
    var userid = req.session.user.userid;
    var dstype = req.body.dstype;

    var params = {
        dsid: dsid,
        folderids: folderids,
        userid: userid,
        dstype: dstype
    };

    fileresourcedao.bindfolders(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        if (err.code == 'ER_DUP_ENTRY') {
            return res.json({
                code: -1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    });
});

router.post('/finish', function (req, res, next) {
    var dsid = req.body.dsid;

    var params = {
        dsid: dsid
    };

    datasetdao.finish(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        return res.json({
            code: 0
        });
    });
});

router.post('/publish', function (req, res, next) {
    var dsid = req.body.dsid;
    var dstype = req.body.dstype;

    var params = {
        dsid: dsid,
        dstype: dstype
    };

    datasetdao.publish(params).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        return res.json({
            code: 0
        });
    });
});

router.post('/update', function (req, res, next) {
    var dsid = req.body.dsid;
    var dsname = req.body.dsname;
    var dstags = req.body.dstags;
    var description = req.body.description;

    var params = {};
    if (dsname != undefined && dsname != '') {
        params.dsname = dsname;
    }
    if (dstags != undefined && dstags != '') {
        params.dstags = dstags;
    }

    if (description != undefined && description != '') {
        params.description = description;
    }
    datasetdao.update_dsinfo(params, dsid).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        return res.json({
            code: 0
        });
    });
});


router.get('/datasource', function (req, res, next) {
    var page = req.query.page || 1; //页号
    var limit = req.query.limit || 10; //页大小
    var key = req.query.key || ''; //查询条件
    var condition = {
        page: parseInt(page),
        limit: parseInt(limit),
        key: key
    };
    datasetdao.resourcelist(condition).then(function (data) {
        res.json({
            "code": 0,
            "msg": "",
            count: data[0][0].length,
            data: data[0][0]
        });
    }, function (error) {
        res.json({
            "code": 1,
            "msg": error.message
        });
    })
});

router.post('/delete', function (req, res, next) {
    var dsid = req.body.dsid;
    datasetdao.delete(dsid).then(function (data) {
        console.log(data);

        if (data) {
            return res.json({
                code: 1
            });
        } else {
            return res.json({
                code: 0
            });
        }
    }, function (err) {
        return res.json({
            code: 0
        });
    });
});

router.post('/refreshdirectory', function (req, res, next) {

    /* services.import(); */
    services.filedisplay();
    return res.json({
        code: 1
    });
});
module.exports = router;